package com.atguigu.edu.mapper;


import java.util.List;

import com.atguigu.edu.realtime.common.bean.Traffic_Statistics_From_Each_Hour;
import com.atguigu.edu.realtime.common.bean.Traffic_Statistics_From_Each_Source;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;


/**
 * @author Felix
 * @date 2024/11/11
 * 交易域统计mapper接口
 */

@Mapper
public interface Traffic_StatisticsMapper {
    //某日总交易额
    // TODO: 2024/11/12 通过select注解的方式, 各来源流量统计 ,参数为date
    @Select("select sc,\n" +
            "       sum(uv_ct) uv_ct,\n" +
            "       sum(sv_ct) sv_ct,\n" +
            "       round(sum(pv_ct)/sum(sv_ct),2) avg_pv_sv,\n" +
            "       round(sum(dur_sum)/sum(sv_ct),2) avg_dur_sv\n" +
            "from dws_traffic_vc_ch_ar_is_new_page_view_window\n" +
            "partition par#{date}\n" +
            "group by cur_date,sc")
    List<Traffic_Statistics_From_Each_Source> selectsc(Integer date);

    // TODO: 2024/11/18  流量分时统计
    @Select("select hour(cur_date) hour,\n" +
            "       sum(uv_ct) uv_ct,\n" +
            "       sum(pv_ct) pv_ct,\n" +
            "       round(sum(pv_ct)/sum(sv_ct),2) avg_pv_sv\n" +
            "from dws_traffic_vc_ch_ar_is_new_page_view_window\n" +
            "partition par#{date}\n" +
            "group by hour(cur_date)")
    List<Traffic_Statistics_From_Each_Hour> selecthour(Integer date);

}

